﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;


namespace SigowayCommonLib.ADO
{
    public class DBManager
    {
        private SQLiteConnection sqliteConn = null;

        private static DBManager dbManager = null;

        private static object syncLock = new object();

        public static DBManager GetDBManager()
        {
            if (dbManager == null)
            {
                lock (syncLock)
                {
                    if (dbManager == null)
                    {
                        dbManager = new DBManager();
                    }
                }
            }

            return dbManager;
        }

        public DBManager()
        {
            try
            {
                this.sqliteConn = new SQLiteConnection(string.Format("Data Source= {0}\\WM.db3;Pooling=true;FailIfMissing=false;Version=3", System.Windows.Forms.Application.StartupPath));
            }
            catch { }
        }

        /// <summary>
        /// 增删改数据(异步接口用)
        /// </summary>
        /// <param name="sqlObject"></param>
        /// <returns></returns>
        internal int ExecuteNonQueryAsync(object sqlObject)
        {
            SQLObject sqlObjectTemp = (SQLObject)sqlObject;

            return ExecuteNonQuery(sqlObjectTemp);
        }

        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="sqlObject"></param>
        /// <returns></returns>
        internal int ExecuteNonQuery(SQLObject sqlObject)
        {
            IList<IEnumerable<SQLiteParameter>> sqlParameters = null;

            if (sqlObject.Parameter != null)
            {
                sqlParameters = new List<IEnumerable<SQLiteParameter>>() { sqlObject.Parameter };
            }
            else
            {
                sqlParameters = sqlObject.ParameterList;
            }

            return ExecuteNonQuery(sqlObject.SQLText, sqlParameters);
        }

        private int ExecuteNonQuery(string sqlText, IList<IEnumerable<SQLiteParameter>> parameters)
        {
            int result = 0;
            try
            {
                if (this.sqliteConn != null
                    && this.sqliteConn.State == System.Data.ConnectionState.Closed)
                {
                    this.sqliteConn.Open();
                }

                using (var myTransaction = this.sqliteConn.BeginTransaction())
                {
                    try
                    {
                        using (var sqliteCommand = new SQLiteCommand(this.sqliteConn))
                        {
                            sqliteCommand.CommandText = sqlText;

                            if (parameters == null || parameters.Count == 0)
                            {
                                result = sqliteCommand.ExecuteNonQuery();
                            }
                            else
                            {
                                foreach (IEnumerable<SQLiteParameter> parameter in parameters)
                                {
                                    if (parameter != null && parameter.Count() > 0)
                                    {
                                        sqliteCommand.Parameters.AddRange(parameter.ToArray<SQLiteParameter>());
                                    }
                                    result += sqliteCommand.ExecuteNonQuery();
                                }
                            }
                        }
                        myTransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        myTransaction.Rollback();
                        throw ex;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                LogManager.WriteLine(ex.ToString());
                throw ex;
            }
            finally
            {
                if (this.sqliteConn != null)
                {
                    //this.sqliteConn.Close();
                }
            }
        }

        /// <summary>
        /// 检索数据
        /// </summary>
        /// <param name="sqlObject"></param>
        /// <returns></returns>
        internal DataTable ExecuteQuery(SQLObject sqlObject)
        {
            return ExecuteQuery(sqlObject.SQLText, sqlObject.Parameter);
        }

        private DataTable ExecuteQuery(string sqlText, IEnumerable<SQLiteParameter> parameters)
        {
            DataTable datResult = null;
            try
            {
                if (this.sqliteConn != null
                    && this.sqliteConn.State == System.Data.ConnectionState.Closed)
                {
                    this.sqliteConn.Open();
                }

                using (var sqliteCommand = new SQLiteCommand(this.sqliteConn))
                {
                    sqliteCommand.CommandText = sqlText;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        sqliteCommand.Parameters.AddRange(parameters.ToArray<SQLiteParameter>());
                    }
                    SQLiteDataReader dataReader = sqliteCommand.ExecuteReader();
                    if (dataReader.HasRows)
                    {
                        datResult = CreateTable(dataReader);
                        int fieldCount = dataReader.FieldCount;
                        object[] objRow;
                        while (dataReader.Read())
                        {
                            objRow = new object[fieldCount];
                            for (int nColIndex = 0; nColIndex < fieldCount; nColIndex++)
                            {
                                objRow[nColIndex] = dataReader.GetValue(nColIndex);
                            }
                            datResult.Rows.Add(objRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogManager.WriteLine(ex.ToString());
                throw ex;
            }
            finally
            {
                if (this.sqliteConn != null)
                {
                    //this.sqliteConn.Close();
                }
            }
            return datResult;
        }

        /// <summary>
        /// 创建DataTable
        /// </summary>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        private static DataTable CreateTable(SQLiteDataReader dataReader)
        {
            DataTable datResult = new DataTable();
            DataTable schemaTable = dataReader.GetSchemaTable();
            foreach (DataRow item in schemaTable.Rows)
            {
                datResult.Columns.Add(item["ColumnName"].ToString(), Type.GetType(item["DataType"].ToString()));
                if (string.IsNullOrEmpty(datResult.TableName))
                {
                    datResult.TableName = item["BaseTableName"].ToString();
                }
            }
            return datResult;
        }

        #region GetSingleValue
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlObject"></param>
        /// <returns></returns>
        internal object GetSingleValue(SQLObject sqlObject)
        {
            SQLObject sqlObjectTemp = (SQLObject)sqlObject;

            return GetSingleValue(sqlObjectTemp.SQLText, sqlObjectTemp.Parameter);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private object GetSingleValue(string sqlText, IEnumerable<SQLiteParameter> parameters)
        {
            try
            {
                if (this.sqliteConn != null
                    && this.sqliteConn.State == System.Data.ConnectionState.Closed)
                {
                    this.sqliteConn.Open();
                }

                using (var sqliteCommand = new SQLiteCommand(this.sqliteConn))
                {
                    sqliteCommand.CommandText = sqlText;
                    if (parameters != null && parameters.Count() > 0)
                    {
                        sqliteCommand.Parameters.AddRange(parameters.ToArray<SQLiteParameter>());
                    }

                    return sqliteCommand.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                LogManager.WriteLine(ex.ToString());
                throw ex;
            }
            finally
            {
                if (this.sqliteConn != null)
                {
                    //this.sqliteConn.Close();
                }
            }
        }
        #endregion
    }

    public struct SQLObject
    {
        private string strSqlText;

        public string SQLText
        {
            get { return strSqlText; }
        }

        private IEnumerable<SQLiteParameter> sqliteParameter;
        public IEnumerable<SQLiteParameter> Parameter
        {
            get { return sqliteParameter; }
        }


        private IList<IEnumerable<SQLiteParameter>> sqliteParameterList;
        public IList<IEnumerable<SQLiteParameter>> ParameterList
        {
            get { return sqliteParameterList; }
        }

        public SQLObject(string sqlText, IDictionary<string, object> parameter) : this(sqlText, parameter, null)
        {
        }

        public SQLObject(string sqlText, IDictionary<string, object> parameter, IList<IDictionary<string, object>> parameterList)
        {
            strSqlText = sqlText;

            sqliteParameter = parameter == null ? null : from item in parameter
                                                         select new SQLiteParameter(item.Key, item.Value);

            sqliteParameterList = null;
            if (parameterList == null)
            {
                return;
            }

            sqliteParameterList = new List<IEnumerable<SQLiteParameter>>();
            foreach (IDictionary<string, object> parameterItem in parameterList)
            {
                sqliteParameterList.Add(from item in parameterItem select new SQLiteParameter(item.Key, item.Value));
            }
        }
    }
}
